package com.witmore.neutron.ai.web.tool;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.witmore.neutron.ai.db.entity.FinanceDO;
import com.witmore.neutron.ai.db.mapper.FinanceMapper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelToMarkdownService {

    @Autowired
    private FinanceMapper financeMapper;
    public String convertExcelToMarkdown(File excelFile,  String orgName,  int year, int month) throws IOException {
        StringBuilder markdownBuilder = new StringBuilder();
        
        try (FileInputStream fis = new FileInputStream(excelFile);
             Workbook workbook = new HSSFWorkbook(fis)) {
            // 查询当前企业当前月份的sheet
            FinanceDO financeDO = financeMapper.selectOne(new LambdaQueryWrapper<FinanceDO>()
                    .eq(FinanceDO::getOrgName, orgName)
                    .eq(FinanceDO::getMonth, month)
                    .eq(FinanceDO::getYear, year)
            );
            // 利润
            String profit = null;
            // 资产负债
            String asset = null;
            // 现金流
            String cash_flow = null;

            // Process each sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                // 每个sheet
                SheetEntity sheetEntity = convertSheetToMarkdown(sheet);
                if (sheetEntity.getTitle().equalsIgnoreCase("资产负债表")){
                    asset = sheetEntity.getContent();
                }
                if (sheetEntity.getTitle().equalsIgnoreCase("现金流量表")){
                    cash_flow = sheetEntity.getContent();
                }
                if (sheetEntity.getTitle().equalsIgnoreCase("利润表")){
                    profit = sheetEntity.getContent();
                }
                markdownBuilder.append(sheetEntity.getContent());
                // Add separator between sheets if not the last one
                if (i < workbook.getNumberOfSheets() - 1) {
                    markdownBuilder.append("\n\n---\n\n");
                }
            }
            // 如果没有数据，插入
            if (financeDO == null) {
                financeDO = new FinanceDO();
                financeDO.setOrgName(orgName);
                financeDO.setMonth(month);
                financeDO.setYear(year);
                financeDO.setProfit(profit);
                financeDO.setAssets(asset);
                financeDO.setCashFlow(cash_flow);
                financeDO.setCreateTime(LocalDateTime.now());
                financeDO.setUpdateTime(LocalDateTime.now());
//                financeMapper.insert(financeDO);
            }else {
                // 如果有数据，更新
                financeDO.setProfit(profit);
                financeDO.setAssets(asset);
                financeDO.setCashFlow(cash_flow);
                financeDO.setUpdateTime(LocalDateTime.now());
//                financeMapper.updateById(financeDO);
            }
        }
        return markdownBuilder.toString();
    }

    private SheetEntity convertSheetToMarkdown(Sheet sheet) {
        StringBuilder sheetBuilder = new StringBuilder();

        // Add sheet name as header
        sheetBuilder.append("# ").append(sheet.getSheetName()).append("\n\n");

        // Process rows
        List<List<String>> tableData = new ArrayList<>();
        int maxColumns = 0;

        for (Row row : sheet) {
            List<String> rowData = new ArrayList<>();

            for (Cell cell : row) {
                rowData.add(getCellValueAsString(cell));
            }

            if (rowData.size() > maxColumns) {
                maxColumns = rowData.size();
            }

            tableData.add(rowData);
        }

        // Create markdown table
        if (!tableData.isEmpty()) {
            // Use first row as header if available
            List<String> headers = tableData.isEmpty() ? new ArrayList<>() : tableData.get(0);

            // Table header
            sheetBuilder.append("|");
            for (int i = 0; i < maxColumns; i++) {
                if (i < headers.size() && !headers.get(i).isEmpty()) {
                    sheetBuilder.append(" ").append(headers.get(i)).append(" |");
                } else {
                    sheetBuilder.append(" Column ").append(i + 1).append(" |");
                }
            }
            sheetBuilder.append("\n");

            // Table separator
            sheetBuilder.append("|");
            for (int i = 0; i < maxColumns; i++) {
                sheetBuilder.append("--- |");
            }
            sheetBuilder.append("\n");

            // Table content (skip the header row which is already used)
            for (int rowIndex = 1; rowIndex < tableData.size(); rowIndex++) {
                List<String> row = tableData.get(rowIndex);
                sheetBuilder.append("|");
                for (String cell : row) {
                    sheetBuilder.append(" ").append(cell).append(" |");
                }
                // Fill empty cells if row has fewer columns
                for (int i = row.size(); i < maxColumns; i++) {
                    sheetBuilder.append("  |");
                }
                sheetBuilder.append("\n");
            }
        }

        SheetEntity sheetEntity = new SheetEntity();
        sheetEntity.setTitle(sheet.getSheetName());
        sheetEntity.setContent(sheetBuilder.toString());
        return sheetEntity;
    }
    
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}